import os
import pandas as pd
import io
import uuid

from flask import Flask, request, jsonify, render_template, redirect, url_for, send_file, flash
from flask_sqlalchemy import SQLAlchemy
from datetime import datetime
from openpyxl.styles import Alignment, PatternFill, Border, Side
from openpyxl.utils import get_column_letter

app = Flask(__name__)

# 配置数据库连接 - MySQL
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://root:zhongtai123?@localhost:3306/gateway_db?charset=utf8mb4'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.config['MAX_CONTENT_LENGTH'] = 16 * 1024 * 1024  # 16MB
# app.config['SECRET_KEY'] = 'your_secret_key_here'
app.config['UPLOAD_FOLDER'] = r'C:\Users\wb_wangyang\PycharmProjects\flask_cases\uploads'

# 创建上传目录
os.makedirs(app.config['UPLOAD_FOLDER'], exist_ok=True)

# 初始化数据库
db = SQLAlchemy(app)


class GatewayInfo(db.Model):
    __tablename__ = 'gateway_info'

    # 基本信息字段
    id = db.Column(db.Integer, primary_key=True)
    version = db.Column(db.String(50), nullable=False)  # 新增版本号字段
    system_name = db.Column(db.String(100), nullable=False)  # 系统名称字段，必填
    case_id = db.Column(db.String(50), nullable=False)  # 不再单独唯一
    module = db.Column(db.String(50), nullable=False)
    obj = db.Column(db.String(50))
    case_level = db.Column(db.String(50))
    related_requirement_id = db.Column(db.String(50))
    change_point = db.Column(db.String(100))
    operation_sequence = db.Column(db.Integer)  # 操作步骤序号
    title = db.Column(db.String(200), nullable=False)
    expected_result = db.Column(db.Text(2000))

    # 执行结果字段
    execution_result = db.Column(db.String(500))
    bug_id = db.Column(db.String(50))
    execution_version = db.Column(db.String(50))
    notes = db.Column(db.String(500))
    implementer = db.Column(db.String(50))
    implementation_date = db.Column(db.String(50))

    # 自动化相关字段
    is_smoke = db.Column(db.String(10))
    is_automated = db.Column(db.String(10))
    automation_status = db.Column(db.String(20))
    pre_operation = db.Column(db.Text(3000))
    post_operation = db.Column(db.Text(3000))

    # 数据库与接口字段
    db_verification = db.Column(db.Text(3000))
    db_result = db.Column(db.Text(3000))
    url = db.Column(db.String(500))
    request_type = db.Column(db.Text(3000))
    request_params = db.Column(db.Text(3000))
    api_code = db.Column(db.String(20))
    api_msg = db.Column(db.Text(2000))

    # 来源信息
    source_sheet = db.Column(db.String(100))  # 记录来自哪个工作表

    # 时间戳
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

    # 联合唯一约束：case_id + operation_sequence
    __table_args__ = (
        db.UniqueConstraint('version', 'case_id', 'operation_sequence', name='unique_version_case_operation'),
    )


# 创建数据库表
with app.app_context():
    db.create_all()


# 辅助函数：获取所有系统名称
def get_all_systems():
    systems = db.session.query(GatewayInfo.system_name).distinct().order_by(GatewayInfo.system_name).all()
    return [s[0] for s in systems]


# 字段说明
def get_field_descriptions():
    return {
        'system_name': '用例所属的系统名称，必填项',
        'version': '用例所属版本,必填项',
        'case_id': '测试用例的编号，可包含多个操作步骤',
        'operation_sequence': '用例步骤的序号，与用例编号组合形成唯一标识',
        'module': '测试用例所属的功能模块',
        'obj': '测试对象，如具体接口、页面等',
        'case_level': '用例的重要程度或优先级',
        'related_requirement_id': '关联的需求文档编号',
        'change_point': '相关的变更内容描述',
        'title': '测试用例的标题或简要描述',
        'expected_result': '测试用例的预期结果',
        'execution_result': '实际执行结果',
        'bug_id': '关联的缺陷编号',
        'execution_version': '执行测试的版本号',
        'notes': '备注信息',
        'implementer': '执行者',
        'implementation_date': '执行日期',
        'is_smoke': '是否为冒烟测试用例',
        'is_automated': '是否为自动化用例',
        'automation_status': '自动化实现状态',
        'pre_operation': '执行前的准备操作',
        'post_operation': '执行后的清理操作',
        'db_verification': '需要进行的数据库验证',
        'db_result': '数据库验证结果',
        'url': '接口测试的URL地址',
        'request_type': '接口请求方法类型',
        'request_params': '接口请求参数',
        'api_code': '接口返回的状态码',
        'api_msg': '接口返回的消息',
        'source_sheet': '数据来源的Excel工作表名称'
    }


# 首页 - 展示网关信息列表（带分页）
@app.route('/')
def index():
    # 获取分页参数
    page = request.args.get('page', 1, type=int)
    per_page = 15  # 每页15条记录

    # 获取搜索参数
    search_system = request.args.get('system_name', '')
    search_case_id = request.args.get('case_id', '')
    search_module = request.args.get('module', '')
    search_version = request.args.get('version', '')
    message = request.args.get('message', '')
    message_type = request.args.get('message_type', '')

    # 构建查询
    query = GatewayInfo.query

    # 应用搜索条件
    if search_system:
        query = query.filter(GatewayInfo.system_name == search_system)
    if search_case_id:
        query = query.filter(GatewayInfo.case_id.like(f'%{search_case_id}%'))
    if search_module:
        query = query.filter(GatewayInfo.module.like(f'%{search_module}%'))
    if search_version:
        query = query.filter(GatewayInfo.version.like(f'%{search_version}%'))

    # 执行分页查询 - 按case_id和操作序列排序
    pagination = query.order_by(GatewayInfo.case_id, GatewayInfo.operation_sequence).paginate(
        page=page, per_page=per_page, error_out=False)
    gateway_info_list = pagination.items

    # 获取所有系统名称（用于筛选下拉框）
    system_list = get_all_systems()
    if message and message_type:

        return render_template('index.html',
                               gateway_info_list=gateway_info_list,
                               pagination=pagination,
                               search_system=search_system,
                               search_version=search_version,
                               search_case_id=search_case_id,
                               search_module=search_module,
                               system_list=system_list,
                               field_descriptions=get_field_descriptions(),
                               message=message,
                               message_type=message_type
                               )
    else:
        return render_template('index.html',
                               gateway_info_list=gateway_info_list,
                               pagination=pagination,
                               search_system=search_system,
                               search_version=search_version,
                               search_case_id=search_case_id,
                               search_module=search_module,
                               system_list=system_list,
                               field_descriptions=get_field_descriptions()
                               )


# 添加网关信息页面
@app.route('/add')
def add():
    # 获取所有系统名称（用于搜索建议）
    system_list = get_all_systems()
    return render_template('add_gateway_info.html',
                           system_list=system_list,
                           field_descriptions=get_field_descriptions())


# 复制用例功能
@app.route('/copy/<int:id>')
def copy_case(id):
    original = GatewayInfo.query.get_or_404(id)

    # 创建新的复制对象
    new_case = GatewayInfo(
        system_name=original.system_name,
        version=original.version,
        case_id=original.case_id,  # 保留原用例ID
        module=original.module,
        obj=original.obj,
        case_level=original.case_level,
        related_requirement_id=original.related_requirement_id,
        change_point=original.change_point,
        # 操作序列自动加1000作为临时值，用户可以手动调整
        operation_sequence=original.operation_sequence + 1000,
        title=f"复制：{original.title}",
        expected_result=original.expected_result,
        execution_result=original.execution_result,
        bug_id=original.bug_id,
        execution_version=original.execution_version,
        notes=original.notes,
        implementer=original.implementer,
        implementation_date=original.implementation_date,
        is_smoke=original.is_smoke,
        is_automated=original.is_automated,
        automation_status=original.automation_status,
        pre_operation=original.pre_operation,
        post_operation=original.post_operation,
        db_verification=original.db_verification,
        db_result=original.db_result,
        url=original.url,
        request_type=original.request_type,
        request_params=original.request_params,
        api_code=original.api_code,
        api_msg=original.api_msg,
        source_sheet="复制生成"
    )

    try:
        db.session.add(new_case)
        db.session.commit()
        return redirect(url_for('edit', id=new_case.id) + '?copy=1')
    except Exception as e:
        db.session.rollback()
        return redirect(url_for('index', error=f'复制失败: {str(e)}'))


# 保存新增的网关信息
@app.route('/save', methods=['POST'])
def save():
    try:
        # 检查用例编号是否已存在
        existing = GatewayInfo.query.filter_by(case_id=request.form['case_id'], version=request.form['version'], operation_sequence=request.form['operation_sequence']).first()
        if existing:
            return redirect(url_for('add', error=f'用例编号 {request.form["case_id"]} 已存在'))

        # 创建新记录
        new_info = GatewayInfo(
            system_name=request.form['system_name'],
            version=request.form['version'],
            case_id=request.form['case_id'],
            module=request.form['module'],
            obj=request.form.get('obj', ''),
            case_level=request.form.get('case_level', ''),
            related_requirement_id=request.form.get('related_requirement_id', ''),
            change_point=request.form.get('change_point', ''),
            operation_sequence=int(request.form['operation_sequence']),
            title=request.form['title'],
            expected_result=request.form['expected_result'],
            execution_result=request.form.get('execution_result', ''),
            bug_id=request.form.get('bug_id', ''),
            execution_version=request.form.get('execution_version', ''),
            notes=request.form.get('notes', ''),
            implementer=request.form.get('implementer', ''),
            implementation_date=request.form.get('implementation_date', ''),
            is_smoke=request.form.get('is_smoke', ''),
            is_automated=request.form['is_automated'],
            automation_status=request.form['automation_status'],
            pre_operation=request.form.get('pre_operation', ''),
            post_operation=request.form.get('post_operation', ''),
            db_verification=request.form.get('db_verification', ''),
            db_result=request.form.get('db_result', ''),
            url=request.form.get('url', ''),
            request_type=request.form.get('request_type', ''),
            request_params=request.form.get('request_params', ''),
            api_code=request.form.get('api_code', ''),
            api_msg=request.form.get('api_msg', '')
        )

        db.session.add(new_info)
        db.session.commit()
        return redirect(url_for('view_case', id=new_info.id, message=f'用例 {request.form["case_id"]} 添加成功', message_type="success"))
    except Exception as e:
        db.session.rollback()
        return redirect(url_for('add', error=f'添加失败: {str(e)}'))


# 编辑网关信息页面
@app.route('/edit/<int:id>')
def edit(id):
    gateway_info = GatewayInfo.query.get_or_404(id)
    system_list = get_all_systems()
    return render_template('edit_gateway_info.html',
                           gateway_info=gateway_info,
                           system_list=system_list,
                           field_descriptions=get_field_descriptions())


# 更新网关信息
@app.route('/update/<int:id>', methods=['POST'])
def update(id):
    try:
        gateway_info = GatewayInfo.query.get_or_404(id)

        # 检查用例编号是否已被其他记录使用
        if gateway_info.case_id != request.form['case_id']:
            existing = GatewayInfo.query.filter_by(case_id=request.form['case_id']).first()
            if existing:
                return redirect(url_for('edit', id=id, error=f'用例编号 {request.form["case_id"]} 已存在'))

        # 更新记录
        gateway_info.system_name = request.form['system_name']
        gateway_info.version = request.form['version']
        gateway_info.case_id = request.form['case_id']
        gateway_info.module = request.form['module']
        gateway_info.obj = request.form.get('obj', '')
        gateway_info.case_level = request.form.get('case_level', '')
        gateway_info.related_requirement_id = request.form.get('related_requirement_id', '')
        gateway_info.change_point = request.form.get('change_point', '')
        gateway_info.operation_sequence = int(request.form['operation_sequence'])
        gateway_info.title = request.form['title']
        gateway_info.expected_result = request.form['expected_result']
        gateway_info.execution_result = request.form.get('execution_result', '')
        gateway_info.bug_id = request.form.get('bug_id', '')
        gateway_info.execution_version = request.form.get('execution_version', '')
        gateway_info.notes = request.form.get('notes', '')
        gateway_info.implementer = request.form.get('implementer', '')
        gateway_info.implementation_date = request.form.get('implementation_date', '')
        gateway_info.is_smoke = request.form.get('is_smoke', '')
        gateway_info.is_automated = request.form['is_automated']
        gateway_info.automation_status = request.form['automation_status']
        gateway_info.pre_operation = request.form.get('pre_operation', '')
        gateway_info.post_operation = request.form.get('post_operation', '')
        gateway_info.db_verification = request.form.get('db_verification', '')
        gateway_info.db_result = request.form.get('db_result', '')
        gateway_info.url = request.form.get('url', '')
        gateway_info.request_type = request.form.get('request_type', '')
        gateway_info.request_params = request.form.get('request_params', '')
        gateway_info.api_code = request.form.get('api_code', '')
        gateway_info.api_msg = request.form.get('api_msg', '')
        gateway_info.updated_at = datetime.utcnow()

        db.session.commit()
        # flash('用例修改成功！', 'success')
        return redirect(url_for('view_case', id=id, message=f'用例 {gateway_info.case_id} 更新成功', message_type="success"))
    except Exception as e:
        db.session.rollback()
        # flash(f'修改失败：{str(e)}', 'danger')
        return redirect(url_for('edit', id=id, error=f'更新失败: {str(e)}', message="用例更新失败", message_type="danger"))


@app.route('/delete/<int:id>', methods=['POST', "GET"])  # 改为POST方法
def delete_case(id):
    case = GatewayInfo.query.get_or_404(id)

    if request.method == 'GET':
        return redirect(url_for('index', id=id, message=f'用例 {case.case_id} 已删除', message_type="success"))
    else:
        try:
            db.session.delete(case)
            db.session.commit()
            # 返回JSON响应而不是重定向
            return jsonify({
                'success': True,
                'message': '用例已成功删除'
            })
        except Exception as e:
            db.session.rollback()
            app.logger.error(f"删除失败: {str(e)}")
            return jsonify({
                'success': False,
                'message': str(e)
            }), 400


@app.route('/import')
def import_page():
    system_list = get_all_systems()
    return render_template('import.html',
                           system_list=system_list,
                           field_descriptions=get_field_descriptions())


@app.route('/import', methods=['POST'])
def import_data():
    try:
        # 验证是否选择了文件
        if 'file' not in request.files:
            return redirect(url_for('import_page', error='请选择要导入的Excel文件'))

        file = request.files['file']
        system_name = request.form.get('system_name', '').strip()
        version = request.form.get('version', '').strip()

        # 验证系统名称
        if not system_name:
            return redirect(url_for('import_page', error='请输入系统名称'))

        if not version:
            return redirect(url_for('index', error='请输入版本号'))

        # 验证文件
        if file.filename == '':
            return redirect(url_for('import_page', error='请选择要导入的Excel文件'))

        if not (file.filename.endswith('.xlsx') or file.filename.endswith('.xls')):
            return redirect(url_for('import_page', error='请上传Excel文件（.xlsx 或 .xls 格式）'))

        # 保存文件
        filename = f"{uuid.uuid4()}_{file.filename}"
        filepath = os.path.join(app.config['UPLOAD_FOLDER'], filename)
        file.save(filepath)

        # 读取Excel文件
        try:
            xls = pd.ExcelFile(filepath)
            total_sheets = len(xls.sheet_names)
            processed_sheets = 0
            imported_count = 0
            skipped_count = 0
            skipped_sheets = []

            # 处理每个工作表
            for sheet_name in xls.sheet_names:
                # 读取工作表数据
                df = pd.read_excel(xls, sheet_name=sheet_name)

                # 检查是否包含必要的列
                required_columns = ['用例编号', '标题']
                if not all(col in df.columns for col in required_columns):
                    skipped_sheets.append(f"工作表 '{sheet_name}' 缺少必要的列（用例编号或标题）")
                    continue

                processed_sheets += 1
                sheet_imported = 0
                sheet_skipped = 0

                # 处理每行数据
                for index, row in df.iterrows():
                    try:
                        case_id = str(row['用例编号']).strip() if pd.notna(row['用例编号']) else ''
                        operation_sequence = str(row['操作序列']).strip() if pd.notna(row['操作序列']) else ''
                        title = str(row['标题']).strip() if pd.notna(row['标题']) else ''

                        # 验证必要字段
                        if not case_id or not title:
                            sheet_skipped += 1
                            continue

                        # 检查用例编号是否已存在
                        existing = GatewayInfo.query.filter_by(case_id=case_id, operation_sequence=operation_sequence).first()
                        if existing:
                            sheet_skipped += 1
                            continue

                        # 创建新记录
                        new_info = GatewayInfo(
                            system_name=system_name,
                            version=version,
                            case_id=case_id,
                            module=str(row['模块']).strip() if '模块' in df.columns and pd.notna(row['模块']) else '',
                            obj=str(row['对象']).strip() if '对象' in df.columns and pd.notna(row['对象']) else '',
                            case_level=str(row['用例分级']).strip() if '用例分级' in df.columns and pd.notna(row['用例分级']) else '',
                            related_requirement_id=str(row['关联需求编号']).strip() if '关联需求编号' in df.columns and pd.notna(row['关联需求编号']) else '',
                            change_point=str(row['变更点']).strip() if '变更点' in df.columns and pd.notna(row['变更点']) else '',
                            operation_sequence=int(row['操作序列']) if '操作序列' in df.columns and pd.notna(row['操作序列']) else 0,
                            title=title,
                            expected_result=str(row['预期结果']).strip() if '预期结果' in df.columns and pd.notna(row['预期结果']) else '',
                            execution_result=str(row['执行结果']).strip() if '执行结果' in df.columns and pd.notna(row['执行结果']) else '',
                            bug_id=str(row['bug编号']).strip() if 'bug编号' in df.columns and pd.notna(row['bug编号']) else '',
                            execution_version=str(row['执行版本']).strip() if '执行版本' in df.columns and pd.notna(row['执行版本']) else '',
                            notes=str(row['备考']).strip() if '备考' in df.columns and pd.notna(row['备考']) else '',
                            implementer=str(row['实施者']).strip() if '实施者' in df.columns and pd.notna(row['实施者']) else '',
                            implementation_date=str(row['实施日']).strip() if '实施日' in df.columns and pd.notna(row['实施日']) else '',
                            is_smoke=str(row['是否冒烟']).strip() if '是否冒烟' in df.columns and pd.notna(row['是否冒烟']) else '',
                            is_automated=str(row['是否自动化']).strip() if '是否自动化' in df.columns and pd.notna(row['是否自动化']) else '',
                            automation_status=str(row['自动化状态']).strip() if '自动化状态' in df.columns and pd.notna(row['自动化状态']) else '',
                            pre_operation=str(row['前置操作']).strip() if '前置操作' in df.columns and pd.notna(row['前置操作']) else '',
                            post_operation=str(row['后置操作']).strip() if '后置操作' in df.columns and pd.notna(row['后置操作']) else '',
                            db_verification=str(row['数据库校验']).strip() if '数据库校验' in df.columns and pd.notna(row['数据库校验']) else '',
                            db_result=str(row['数据库结果']).strip() if '数据库结果' in df.columns and pd.notna(row['数据库结果']) else '',
                            url=str(row['URL']).strip() if 'URL' in df.columns and pd.notna(row['URL']) else '',
                            request_type=str(row['请求类型']).strip() if '请求类型' in df.columns and pd.notna(row['请求类型']) else '',
                            request_params=str(row['请求参数']).strip() if '请求参数' in df.columns and pd.notna(row['请求参数']) else '',
                            api_code=str(row['api_code']).strip() if 'api_code' in df.columns and pd.notna(row['api_code']) else '',
                            api_msg=str(row['api_msg']).strip() if 'api_msg' in df.columns and pd.notna(row['api_msg']) else '',
                            source_sheet=sheet_name
                        )

                        db.session.add(new_info)
                        sheet_imported += 1
                        imported_count += 1

                        # 每10条记录提交一次
                        if imported_count % 10 == 0:
                            db.session.commit()
                    except Exception as e:
                        sheet_skipped += 1
                        continue

            # 最终提交
            db.session.commit()

            # 清理上传的文件
            # if os.path.exists(filepath):
            #     os.remove(filepath)

            # 构建结果消息
            message = f"导入完成！\n"
            message += f"共处理 {total_sheets} 个工作表，其中 {processed_sheets} 个有效工作表，{len(skipped_sheets)} 个工作表被跳过\n"
            message += f"成功导入 {imported_count} 条记录，跳过 {skipped_count + imported_count - imported_count} 条记录（已存在或数据不完整）\n"
            message += f"所有记录已关联到系统：{system_name}"

            if skipped_sheets:
                message += "\n跳过的工作表原因：\n" + "\n".join(skipped_sheets)

            return redirect(url_for('import_page', message=message))
        except Exception as e:
            # 清理上传的文件
            # if os.path.exists(filepath):
            #     os.remove(filepath)
            return redirect(url_for('import_page', error=f'处理Excel文件失败: {str(e)}'))
    except Exception as e:
        return redirect(url_for('import_page', error=f'导入失败: {str(e)}'))


field_info = {
    # 基本信息 - 蓝色标题
    '系统名称': {'category': 'basic'},
    '用例编号': {'category': 'api'},
    '操作序列': {'category': 'api'},
    '模块': {'category': 'api'},
    '对象': {'category': 'basic'},
    '用例分级': {'category': 'basic'},
    '关联需求编号': {'category': 'basic'},
    '变更点': {'category': 'basic'},
    '标题': {'category': 'api'},
    '预期结果': {'category': 'basic'},
    '版本号': {'category': 'basic'},  # 新增版本号字段信息
    # 执行结果 - 蓝色标题
    '执行结果': {'category': 'execution'},
    'bug编号': {'category': 'execution'},
    '执行版本': {'category': 'execution'},
    '备考': {'category': 'execution'},
    '实施者': {'category': 'execution'},
    '实施日': {'category': 'execution'},

    # 自动化相关 - 蓝色标题
    '是否冒烟': {'category': 'api'},
    '是否自动化': {'category': 'api'},
    '自动化状态': {'category': 'api'},
    '前置操作': {'category': 'api'},
    '后置操作': {'category': 'api'},

    # 数据库相关 - 蓝色标题
    '数据库校验': {'category': 'api'},
    '数据库结果': {'category': 'api'},

    # 接口相关 - 黄色标题
    'URL': {'category': 'api'},
    '请求类型': {'category': 'api'},
    '请求参数': {'category': 'api'},
    'api_code': {'category': 'api'},
    'api_msg': {'category': 'api'},

    # 其他信息 - 蓝色标题
    '来源工作表': {'category': 'other'}
}


@app.route('/export')
def export_excel():
    try:
        # 获取所有筛选参数
        system_name = request.args.get('system_name', '').strip()
        case_id = request.args.get('case_id', '').strip()
        module = request.args.get('module', '').strip()
        version = request.args.get('version', '').strip()  # 新增版本筛选
        # 强制检查系统名称是否选择
        if not system_name:
            return redirect(url_for('index', error='请先选择系统名称才能导出数据'))
        if not version:
            return redirect(url_for('index', error='请先选择系统版本才能导出数据'))

        # 构建查询
        query = GatewayInfo.query.filter(GatewayInfo.system_name.like(f'%{system_name}%'))

        # 应用其他筛选条件
        if case_id:
            query = query.filter(GatewayInfo.case_id.like(f'%{case_id}%'))
        if module:
            query = query.filter(GatewayInfo.module.like(f'%{module}%'))
        if version:
            query = query.filter(GatewayInfo.version == version)
        # 按模块、用例编号和操作序列排序
        query = query.order_by(GatewayInfo.module, GatewayInfo.case_id, GatewayInfo.operation_sequence)

        # 获取所有符合条件的记录
        records = query.all()

        if not records:
            return redirect(url_for('index', error=f'没有找到系统"{system_name}"的相关数据，无法导出'))

        # 按模块分组数据
        module_data = {}
        for record in records:
            if record.module not in module_data:
                module_data[record.module] = []

            module_data[record.module].append({
                # '系统名称': record.system_name,
                '用例编号': record.case_id,
                '模块': record.module,
                '对象': record.obj,
                '用例分级': record.case_level,
                '关联需求编号': record.related_requirement_id,
                '变更点': record.change_point,
                '操作序列': record.operation_sequence,
                '标题': record.title,
                '预期结果': record.expected_result,
                '执行结果': record.execution_result,
                'bug编号': record.bug_id,
                '执行版本': record.execution_version,
                '备考': record.notes,
                '实施者': record.implementer,
                '实施日': record.implementation_date,
                '是否冒烟': record.is_smoke,
                '是否自动化': record.is_automated,
                '自动化状态': record.automation_status,
                '前置操作': record.pre_operation,
                '后置操作': record.post_operation,
                '数据库校验': record.db_verification,
                '数据库结果': record.db_result,
                'URL': record.url,
                '请求类型': record.request_type,
                '请求参数': record.request_params,
                'api_code': record.api_code,
                'api_msg': record.api_msg,
                # '来源工作表': record.source_sheet
            })

        # 创建Excel文件并设置格式
        output = io.BytesIO()

        # 定义边框样式 - 细实线
        thin_border = Border(
            left=Side(style='thin'),
            right=Side(style='thin'),
            top=Side(style='thin'),
            bottom=Side(style='thin')
        )

        # 使用ExcelWriter和openpyxl引擎来设置格式
        with pd.ExcelWriter(output, engine='openpyxl') as writer:
            # 为每个模块创建一个sheet
            for module_name, data in module_data.items():
                # 创建DataFrame
                df = pd.DataFrame(data)

                # 处理sheet名称，确保不超过31个字符（Excel限制）
                sheet_name = module_name[:31]

                # 将数据写入Excel的对应sheet
                df.to_excel(writer, index=False, sheet_name=sheet_name)

                # 获取当前工作表对象
                worksheet = writer.sheets[sheet_name]
                # 冻结第一行
                worksheet.freeze_panes = 'A2'
                # 定义样式
                # 标题行样式 - 蓝色
                blue_fill = PatternFill(start_color="6495ED", end_color="6495ED", fill_type="solid")
                # 标题行样式 - 黄色（接口相关）
                yellow_fill = PatternFill(start_color="FFFF00", end_color="FFFF00", fill_type="solid")
                # 标题字体样式
                # header_font = Font(bold=True, color="FFFFFF")  # 白色字体，与深色背景对比
                # 单元格对齐方式（垂直居中、自动换行）
                cell_alignment = Alignment(vertical='center', wrap_text=True)

                # 设置标题行格式和列宽
                for col_idx, col_name in enumerate(df.columns, 1):
                    # 获取列字母
                    col_letter = get_column_letter(col_idx)

                    # 设置列宽为10
                    worksheet.column_dimensions[col_letter].width = 10

                    # 设置标题单元格样式
                    cell = worksheet[f'{col_letter}1']

                    # 根据字段类型设置背景颜色
                    if field_info.get(col_name, {}).get('category') == 'api':
                        cell.fill = yellow_fill  # 接口相关字段用黄色
                    else:
                        cell.fill = blue_fill  # 其他字段用蓝色

                    # cell.font = header_font
                    cell.alignment = cell_alignment
                    cell.border = thin_border  # 添加边框

                # 设置所有行高为30，并应用垂直居中和自动换行，以及边框
                for row_idx in range(1, worksheet.max_row + 1):
                    worksheet.row_dimensions[row_idx].height = 30
                    for col_idx in range(1, worksheet.max_column + 1):
                        col_letter = get_column_letter(col_idx)
                        cell = worksheet[f'{col_letter}{row_idx}']
                        cell.alignment = cell_alignment
                        cell.border = thin_border  # 为每个单元格添加边框

        # 将文件指针移到开头
        output.seek(0)

        # 生成文件名
        timestamp = datetime.now().strftime('%Y%m%d_%H%M%S')
        filename = f"{system_name}_{version}"

        # 添加其他筛选条件到文件名
        if case_id:
            filename += f"_用例_{case_id}"
        if module:
            filename += f"_模块_{module}"

        filename += f"_{timestamp}.xlsx"

        # 返回文件
        return send_file(
            output,
            download_name=filename,
            as_attachment=True,
            mimetype="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
        )

    except Exception as e:
        app.logger.error(f"导出Excel时发生错误: {str(e)}")
        return redirect(url_for('index', error=f'导出失败: {str(e)}'))


@app.route('/view/<int:id>')
def view_case(id):
    # 查询指定ID的用例数据
    case = GatewayInfo.query.get_or_404(id)
    message = request.args.get('message')
    message_type = request.args.get('message_type', 'info')
    # 渲染查看页面模板
    return render_template('view.html', case=case, message=message,
                           message_type=message_type)


if __name__ == '__main__':
    app.run(debug=True)
